#!/usr/bin/env python
# -*- coding: UTF-8 -*-

import rospy
import sqlite3


class Auth_Parser:
    def __init__(self):
        self.__permission=["admin","opera"]
        self.__user_database = rospy.get_param('/db_user','/home/aoting/user_config/user_database/user.db')
        # print("self.__user_database: {}".format(self.__user_database))
        self.user_dic_account_list = {} # 用户缓存列表
        self.__connection = sqlite3.connect(self.__user_database)
        self.__connection.text_factory=str
        self.__CreateTable()

    def __ConnectDatabase(self):
        '''
            新建/连接数据库
        '''
        try:
            self.__connection = sqlite3.connect(self.__user_database)
            # rospy.loginfo("[Auth_Parser]Opened database successfully!")
        except sqlite3.Error as error:
            rospy.logerr("[Auth_Parser]Failed to connect database!".format(str(error)))

    def __CreateTable(self):
        '''
            新建表USER
        '''
        try:
            cursor = self.__connection.cursor()
            cursor.execute("select name from sqlite_master where type='table' order by name")
            table_data=cursor.fetchall()
            if table_data:
                rospy.loginfo("[Auth_Parser]Table already exist!")
            else:
                cursor.execute("CREATE TABLE USER (ACCOUNT TEXT PRIMARY KEY NOT NULL, PASSWORD TEXT NOT NULL, PERMISSION TEXT NOT NULL)")
                self.__connection.commit()
                self.__connection.close()
                rospy.loginfo("[Auth_Parser]Table created successfully!")
        except sqlite3.Error as error:
            rospy.logerr("[Auth_Parser]Failed to create table: {}".format(str(error)))

    def GetAccountData(self):
        '''
            查询账号密码
        '''
        try:
            # if not self.user_dic_account_list:
            self.__ConnectDatabase()
            cursor = self.__connection.cursor()
            user_cursor = cursor.execute("SELECT ACCOUNT,PASSWORD,PERMISSION from USER")
            # user_dic={}
            # for row in user_cursor:
            #     user_dic[row[0]]=(row[1],row[2])
            for row in user_cursor:
                self.user_dic_account_list[row[0]]=(row[1],row[2])
            
            # rospy.loginfo("[Auth_Parser]Get account data successfully!")
            return self.user_dic_account_list
        except sqlite3.Error as error:
            rospy.logerr("[Auth_Parser]Failed to get account data: {}".format(str(error)))
            return {}

    def CreateAccount(self,account,password,permission):
        '''
            创建账号密码
        '''
        try:

            self.user_dic_account_list.clear() # 数据库发生变化清空用户列表缓存数据

            user_dic=self.GetAccountData()
            if account not in user_dic.keys():  # 帐号不存在则创建          
                cursor = self.__connection.cursor()
                cursor.execute("INSERT INTO USER (ACCOUNT,PASSWORD,PERMISSION) \
                    VALUES ('" + str(account) + "','" + str(password) + "','" + str(permission) +"')")
                self.__connection.commit()
                rospy.loginfo("[Auth_Parser]Account created successfully!")
                self.__connection.close()
            else: # 帐号存在则修改
                cursor = self.__connection.cursor()
                cursor.execute("UPDATE USER set PASSWORD='" + str(password) + "' where ACCOUNT='" + str(account) +"'")
                self.__connection.commit()
                self.__connection.close()                
            return True,"success"
        except sqlite3.Error as error:
            rospy.logerr("[Auth_Parser]Failed to create account data: {}".format(str(error)))
            return False, str(error)

    def DeleteAccount(self,account):
        '''
            删除帐号
        '''
        try:
            self.user_dic_account_list.clear() # 数据库发生变化清空用户列表缓存数据
            
            user_dic = self.GetAccountData()
            if account in user_dic.keys():            
                cursor = self.__connection.cursor()
                cursor.execute("DELETE from USER where ACCOUNT='" + str(account) +"'")
                self.__connection.commit()
                rospy.loginfo("[Auth_Parser]Account deleted successfully!")
                self.__connection.close()
            else: 
                raise Exception("The account does not exist!") 
            return True,"success"
        except sqlite3.Error as error:
            rospy.logerr("[Auth_Parser]Failed to delete account data: {}".format(str(error)))
            return False, str(error)

    def GetPermission(self):
        '''
            获取账户权限类型
        '''
        return self.__permission
